package com.charles.mybatis;

import com.charles.mybatis.Entity.TUser;
import org.junit.jupiter.api.Test;

import java.sql.*;
import java.util.ArrayList;

/**
 * JDBC连接数据库
 */
public class JdbcDemo {

    private static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";

    private static final String DB_URL = "jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true";

    private static String USER = "root";
    private static String PASS = "root";

    public void QueryStatementDemo(){
        Connection conn = null;
        Statement stmt = null;
        ArrayList<TUser> users = new ArrayList<TUser>();

        try {
            //STEP2:注册mysql的驱动
            Class.forName("com.mysql.jdbc.Driver");

            //STEP3:获取一个连接
            System.out.println("Connection to database...");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);

            //STEP4:创建一个查询
            System.out.println("Creating statement...");
            stmt = conn.createStatement();
            String userName = "lison";
            String sql = "SELECT * FROM t_user where user_name ='"+userName+"'";
            ResultSet rs = stmt.executeQuery(sql);
            System.out.println(stmt.toString());

            //STEP5:从resultSet中获取数据并转化成bean
            while (rs.next()){
                System.out.println("----------------------------------");
                TUser user = new TUser();
                user.setRealName(rs.getString("real_name"));
                user.setSex(rs.getByte("sex"));
                user.setMobile(rs.getString("mobile"));
                user.setEmail(rs.getString("email"));
                user.setNote(rs.getString("note"));
                System.out.println(user.toString());
                users.add(user);
            }
            //STEP5:关闭连接
            rs.close();
            stmt.close();
            conn.close();


        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                if (stmt != null)
                    stmt.close();
            }catch (SQLException se){

            }
            try {
                if (conn != null)
                    conn.close();
            }catch (SQLException se){

            }
        }
    }

    /**
     * 预查询
     */
    public void QueryPreparedStatementDemo(){
       Connection conn = null;
       PreparedStatement stmt = null;
        ArrayList<TUser> users = new ArrayList();
        try {
            //STEP2:注册mysql的驱动
            Class.forName("com.mysql.jdbc.Driver");

            //STEP3:获取连接
            System.out.println("Connection to database...");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);

            //STEP4:获取一个查询
            System.out.println("Creating statement...");
            String sql;
            sql = "SELECT * FROM t_user where user_name= ? ";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1,"lison");
            System.out.println(stmt.toString());//打印SQL
            ResultSet rs = stmt.executeQuery();
            //STEP5:从resultSet中获取数据并转化成bean
            while (rs.next()){
                System.out.println("----------------------------------");
                TUser user = new TUser();
                user.setRealName(rs.getString("real_name"));
                user.setSex(rs.getByte("sex"));
                user.setMobile(rs.getString("mobile"));
                user.setEmail(rs.getString("email"));
                user.setNote(rs.getString("note"));
                System.out.println(user.toString());
                users.add(user);
            }
            //STEP5:关闭连接
            rs.close();
            stmt.close();
            conn.close();


        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                if (stmt != null)
                    stmt.close();
            }catch (SQLException se){

            }
            try {
                if (conn != null)
                    conn.close();
            }catch (SQLException se){

            }
        }

    }


    public void updateDemo(){
        Connection conn = null;
        PreparedStatement stmt = null;


        try {
            //STEP2:注册mysql的驱动
            Class.forName("com.mysql.jdbc.Driver");

            //STEP3:获取一个连接
            System.out.println("Connection to database...");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);

            //STEP4:启动手动提交
            conn.setAutoCommit(false);

            //STEP5:创建一个更新
            System.out.println("Creating statement...");
            String sql = "update t_user set mobile=? where user_name = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1,"186995587411");
            stmt.setString(2,"lison");
            System.out.println(stmt.toString());//打印 sql
            int ret = stmt.executeUpdate();
            System.out.println("此次修改影响数据库的行为为："+ret);

            //STEP6:手动提交数据
            conn.commit();

            //STEP7:关闭连接
            stmt.close();
            conn.close();

        } catch (Exception e) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
        }finally {
            try {
                if (stmt != null)
                    stmt.close();
            }catch (SQLException se){

            }
            try {
                if (conn != null)
                    conn.close();
            }catch (SQLException se){

            }
        }
    }

}
